package library.dao.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;
import library.dao.BookDao;
import library.dao.ConnectedDao;
import library.domain.Book;
import library.exception.DataException;

public class BookDaoImpl extends ConnectedDao implements BookDao {

    private static final String CREATE_REQUEST = "INSERT INTO `books` (`name`, `author`, `size`, `extension`, `tags`, `description`) VALUES (?,?,?,?,?,?)";
    private static final String UPDATE_REQUEST = "UPDATE `books` SET `name` = ?, `author` = ?, `size` = ?, `extension` = ?, `tags` = ?, `description` = ? WHERE `identity` = ?";
    private static final String DELETE_REQUEST = "DELETE FROM `books` WHERE `identity` = ?";
    private static final String READ_REQUEST = "SELECT `identity`, `name`, `author`, `size`, `extension`, `tags`, `description` FROM `books` WHERE `identity` = ?";
    private static final String READ_ALL_REQUEST = "SELECT `identity`, `name`, `author`, `size`, `extension`, `tags`, `description` FROM `books`";
    private static final String GET_BY_NAME_REQUEST = "SELECT `identity`, `name`, `author`, `size`, `extension`, `tags`, `description` FROM `books` WHERE `name` LIKE ?";

    public BookDaoImpl(Connection connection) {
        super(connection);
    }

    @Override
    public Integer create(Book entity) throws DataException {
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Integer identity = null;
        try {
            String sql = CREATE_REQUEST;
            statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

            statement.setString(1, entity.getName());
            statement.setString(2, entity.getAuthor());
            statement.setLong(3, entity.getSize());
            statement.setString(4, entity.getExtension());
            statement.setString(5, entity.getTagsAsString());
            statement.setString(6, entity.getDescription());

            statement.executeUpdate();
            resultSet = statement.getGeneratedKeys();
            if (resultSet.next()) {
                identity = resultSet.getInt(1);
                entity.setId(identity);
                return identity;
            } else {
                throw new DataException(new RuntimeException(
                        "Error: RETURN_GENERATED_KEYS"));
            }
        } catch (SQLException e) {
            throw new DataException(e);
        } finally {
            close(resultSet, statement);
        }
    }

    @Override
    public Book read(Integer identity) throws DataException {
        Book book = null;

        PreparedStatement statement = null;
        ResultSet resultSet = null;

        try {
            String sql = READ_REQUEST;
            statement = connection.prepareStatement(sql);
            statement.setInt(1, identity);
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                book = new Book();
                book.setId(resultSet.getInt("identity"));
                book.setName(resultSet.getString("name"));
                book.setAuthor(resultSet.getString("author"));
                book.setSize(resultSet.getLong("size"));
                book.setTags(resultSet.getString("tags").split(";"));
                book.setExtension(resultSet.getString("extension"));
                book.setDescription(resultSet.getString("description"));
            }
            return book;

        } catch (SQLException e) {
            throw new DataException(e);
        } finally {
            close(resultSet, statement);
        }
    }

    @Override
    public List<Book> readAll() throws DataException {
        Book book = null;

        List<Book> list = new LinkedList<Book>();
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        try {
            String sql = READ_ALL_REQUEST;
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                book = new Book();
                book.setId(resultSet.getInt("identity"));
                book.setName(resultSet.getString("name"));
                book.setAuthor(resultSet.getString("author"));
                book.setSize(resultSet.getLong("size"));
                book.setTags(resultSet.getString("tags").split(";"));
                book.setExtension(resultSet.getString("extension"));
                book.setDescription(resultSet.getString("description"));
                list.add(book);
            }
            return list;

        } catch (SQLException e) {
            throw new DataException(e);
        } finally {
            close(resultSet, statement);
        }
    }

    @Override
    public void update(Book value) throws DataException {
        PreparedStatement statement = null;
        try {
            String sql = UPDATE_REQUEST;
            statement = connection.prepareStatement(sql);
            statement.setString(1, value.getName());
            statement.setString(2, value.getAuthor());
            statement.setLong(3, value.getSize());
            statement.setString(4, value.getExtension());
            statement.setString(5, value.getTagsAsString());
            statement.setString(6, value.getDescription());
            statement.setInt(7, value.getId());
            statement.executeUpdate();
        } catch (SQLException e) {
            throw new DataException(e);
        } finally {
            close(null, statement);
        }
    }

    @Override
    public void delete(Integer identity) throws DataException {
        PreparedStatement statement = null;
        try {
            String sql = DELETE_REQUEST;
            statement = connection.prepareStatement(sql);
            statement.setInt(1, identity);
            statement.executeUpdate();
        } catch (SQLException e) {
            throw new DataException(e);
        } finally {
            close(null, statement);
        }
    }

    @Override
    public List<Book> getByName(String name) throws DataException {

        Book book = null;

        List<Book> list = new LinkedList<Book>();
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        try {
            String sql = GET_BY_NAME_REQUEST;
            statement = connection.prepareStatement(sql);
            statement.setString(1, "%" + name + "%");
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                book = new Book();
                book.setId(resultSet.getInt("identity"));
                book.setName(resultSet.getString("name"));
                book.setAuthor(resultSet.getString("author"));
                book.setSize(resultSet.getLong("size"));
                book.setTags(resultSet.getString("tags").split(";"));
                book.setExtension(resultSet.getString("extension"));
                book.setDescription(resultSet.getString("description"));
                list.add(book);
            }
            return list;

        } catch (SQLException e) {
            throw new DataException(e);
        } finally {
            close(resultSet, statement);
        }
    }
}
